/**   
 * @Title: ExcelReader.java 
 * @Package com.git.gather 
 * @Description: excel读取相关工具类，支持xls，与slsx
 * @author fanqinghui100@126.com   
 * @date 2014年6月2日 下午8:19:46 
 * @version V1.0   
 */
package com.git.gather;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel解析类，支持xls,xlsx
 */
public class ExcelReader {

	Logger logger = Logger.getAnonymousLogger();
	// 魔数，标识文件类型
	private static final int MAGIC_NUMBER_XLS = 0xD0CF11E0;
	private static final int MAGIC_NUMBER_XLSX = 0x504B0304;
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	private Workbook workbook = null;

	/**
	 * 构造方法：创建Excel解析类实例
	 * 
	 * @param filename
	 *            文件名
	 * @throws Exception
	 */
	public ExcelReader(String filename) throws Exception {
		try {
			init(new FileInputStream(new File(filename)));
		} catch (FileNotFoundException e) {
			throw new Exception("通过输入流创建Excel解析类时出现异常!", e);
		}
	}

	/**
	 * 构造方法：创建Excel解析类实例
	 * 
	 * @param file
	 *            Excel文件对象
	 * @throws Exception
	 */
	public ExcelReader(File file) throws Exception {
		try {
			init(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			throw new Exception("通过输入流创建Excel解析类时出现异常!", e);
		}
	}

	/**
	 * 构造方法：创建Excel解析类实例
	 * 
	 * @param is
	 *            输入流
	 * @throws Exception 
	 */
	public ExcelReader(InputStream is) throws Exception {
		init(is);
	}

	private void init(InputStream is) throws Exception {
		BufferedInputStream bis = null;
		try /* (BufferedInputStream bis = new BufferedInputStream(is)) */{
			bis = new BufferedInputStream(is);
			bis.mark(0);
			byte[] mnbytes = new byte[4];
			bis.read(mnbytes);
			int magicNumber = 0;
			for (int i = 0; i < mnbytes.length; i++) {
				magicNumber |= (mnbytes[i] >= 0 ? mnbytes[i] : (256 + mnbytes[i])) << (8 * (4 - i - 1));
			}
			bis.reset();
			if (MAGIC_NUMBER_XLS == magicNumber)
				workbook = new HSSFWorkbook(bis);
			else if (MAGIC_NUMBER_XLSX == magicNumber)
				workbook = new XSSFWorkbook(bis);
			else
				throw new Exception("输入流不符合Excel格式!");
		} catch (Exception e) {
			throw new Exception("创建Excel解析类时出现异常!", e);
		}finally{
			if(bis!=null)bis.close();
		}
	}

	/**
	 * 获取全部内容
	 * 
	 * @return
	 * @throws Exception
	 */
	public Map<Integer, List<Map<Integer, String>>> getDataByAll() throws Exception {
		Map<Integer, List<Map<Integer, String>>> sheetMap = new LinkedHashMap<Integer, List<Map<Integer, String>>>();
		int sheetCount = workbook.getNumberOfSheets();
		for (int i = 0; i < sheetCount; i++) {
			List<Map<Integer, String>> rowList = getDataBySheet(i);
			if (rowList != null && rowList.size() > 0) {
				sheetMap.put(i, rowList);
			}
		}
		return sheetMap;
	}

	/**
	 * 获取指定sheet的内容
	 * 
	 * @param sheetNum
	 *            sheet序号，从0开始
	 * @return
	 * @throws Exception
	 */
	public List<Map<Integer, String>> getDataBySheet(int sheetNum) throws Exception {
		checkRange(sheetNum, -1, -1);

		List<Map<Integer, String>> rowList = new ArrayList<Map<Integer, String>>();
		Sheet sheet = workbook.getSheetAt(sheetNum); // 取得相应工作表
		int lastRowNum = sheet.getLastRowNum();
		// 遍历每一行
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				Map<Integer, String> cellMap = getDataByRow0(row);
				if (cellMap != null)
					rowList.add(cellMap);
			}
		}
		return rowList;
	}

	/**
	 * 获取某个sheet中从指定行开始的数据（可用于去除标题行）
	 * 
	 * @param sheetNum
	 *            sheet的序号，从0开始
	 * @param startRowNum
	 *            开始行号，从0开始
	 * @return
	 * @throws Exception
	 */
	public List<Map<Integer, String>> getDataBySheet(int sheetNum, int startRowNum) throws Exception {
		checkRange(sheetNum, startRowNum, -1);

		List<Map<Integer, String>> sheetList = new ArrayList<Map<Integer, String>>();
		Sheet sheet = workbook.getSheetAt(sheetNum);
		int lastRowNum = sheet.getLastRowNum();
		for (int i = startRowNum; i <= lastRowNum; i++) {
			Map<Integer, String> cellMap = getDataByRow0(sheet.getRow(i));
			if (cellMap != null)
				sheetList.add(cellMap);
		}
		return sheetList;
	}

	/**
	 * 获取某一sheet中指定行的数据
	 * 
	 * @param sheetNum
	 *            sheet的序号，从0开始
	 * @param startRowNum
	 *            行号，从0开始
	 * @return
	 * @throws Exception
	 */
	public Map<Integer, String> getDataByRow(int sheetNum, int rowNum) throws Exception {
		checkRange(sheetNum, rowNum, -1);

		Sheet sheet = workbook.getSheetAt(sheetNum);
		return getDataByRow0(sheet.getRow(rowNum));
	}

	/**
	 * 获取某个sheet中某一列的数据
	 * 
	 * @param sheetNum
	 *            sheet的序号，从0开始
	 * @param columnNum
	 *            列序号，从0开始
	 * @return
	 * @throws Exception
	 */
	public Map<Integer, String> getDataByColumn(int sheetNum, int columnNum) throws Exception {
		Sheet sheet = workbook.getSheetAt(sheetNum);
		int lastRowNum = sheet.getLastRowNum();
		Map<Integer, String> cellMap = new LinkedHashMap<Integer, String>();
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			Cell cell = row.getCell(columnNum);
			cellMap.put((int) i, getCellValue(cell));
		}
		return trimMap(cellMap);
	}

	/**
	 * 获取指定单元格的数据
	 * 
	 * @param sheetNum
	 *            sheet序号，从0开始
	 * @param rowNum
	 *            行号，从0开始
	 * @param cellNum
	 *            列号，从0开始
	 * @return
	 * @throws Exception
	 */
	public String getDataByCell(int sheetNum, int rowNum, int cellNum) throws Exception {
		checkRange(sheetNum, rowNum, cellNum);

		Sheet sheet = workbook.getSheetAt(sheetNum);
		Row row = sheet.getRow(rowNum);
		return getCellValue(row.getCell(cellNum));
	}

	/**
	 * 获取当前sheet中合并单元格的个数
	 * 
	 * @param sheet
	 * @return
	 */
	public Integer getMergeCount(Sheet sheet) {
		return sheet.getNumMergedRegions();
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 *            当前sheet对象
	 * @param rowNum
	 *            获取值的行数
	 * @param cellNum
	 *            获取值的列数
	 * @return
	 */
	public String getMergedRegionValue(Sheet sheet, int rowNum, int cellNum) {
		// 获取合并单元格的总个数
		int sheetMergeCount = getMergeCount(sheet);
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (rowNum >= firstRow && rowNum <= lastRow) {
				if (cellNum >= firstColumn && cellNum <= lastColumn) {
					Row row = sheet.getRow(rowNum);
					Cell cell = row.getCell(cellNum);
					return getCellValue(cell);
				}
			}
		}
		return null;
	}

	/**
	 * 获取某一行的数据
	 * 
	 * @param row
	 *            Excel行对象
	 * @return
	 * @throws Exception
	 */
	private Map<Integer, String> getDataByRow0(Row row) throws Exception {
		Map<Integer, String> cellMap = new LinkedHashMap<Integer, String>();
		int lastCellNum = row.getLastCellNum();
		// 遍历每一列
		for (int c = 0; c < lastCellNum; c++) {
			Cell cell = row.getCell(c);
			cellMap.put(c, StringUtils.trim(getCellValue(cell)));
		}
		return trimMap(cellMap);
	}

	/**
	 * 获取单元格的值
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell) {
		String value = "";
		if (null != cell) {
			try {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC: // 数字&日期
					if (DateUtil.isCellDateFormatted(cell)) {
						value = sdf.format(cell.getDateCellValue());
					} else {
						value = String.valueOf(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_STRING: // 字符串
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_BOOLEAN: // Boolean
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA: // 公式
					switch (cell.getCachedFormulaResultType()) {
					case Cell.CELL_TYPE_NUMERIC: // 数字&日期
						if (DateUtil.isCellDateFormatted(cell)) {
							value = sdf.format(cell.getDateCellValue());
						} else {
							value = String.valueOf(cell.getNumericCellValue());
						}
						break;
					case Cell.CELL_TYPE_STRING: // 字符串
						value = cell.getStringCellValue();
						break;
					case Cell.CELL_TYPE_BOOLEAN: // Boolean
						value = String.valueOf(cell.getBooleanCellValue());
						break;
					case Cell.CELL_TYPE_BLANK: // 空值
						break;
					case Cell.CELL_TYPE_ERROR: // 故障
						break;
					default:
						break;
					}
					break;
				case Cell.CELL_TYPE_BLANK: // 空值
					break;
				case Cell.CELL_TYPE_ERROR: // 故障
					break;
				default:
					break;
				}
			} catch (Exception e) {
				value = cell.toString();
			}
		}
		return value;
	}

	/**
	 * 去除无效的行（没有任何数据的行）
	 * 
	 * @param params
	 * @return
	 */
	private Map<Integer, String> trimMap(Map<Integer, String> params) {
		if (params != null && params.size() > 0) {
			Iterator<Integer> iterator = params.keySet().iterator();
			while (iterator.hasNext()) {
				String value = params.get(Integer.valueOf(iterator.next()));
				if (StringUtils.isNotBlank(value)) {
					return params;// 只要一行中有一个单元格有值，就说明这一行数据有用
				}
			}
		}
		return null;
	}

	/**
	 * 检查指定位置是否越界
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @throws Exception
	 */
	private void checkRange(int sheetNum, int rowNum, int cellNum) throws Exception {
		if (sheetNum >= 0) {
			int sheetCount = workbook.getNumberOfSheets();
			if (sheetNum > sheetCount - 1)
				throw new Exception("无效的sheet序号" + sheetNum + "，最大sheet序号为：" + (sheetCount - 1));

			if (rowNum >= 0) {
				Sheet sheet = workbook.getSheetAt(sheetNum);
				int lastRowNum = sheet.getLastRowNum();
				if (rowNum > lastRowNum) {
					throw new Exception("无效的行号" + rowNum + "，当前sheet最大行号为：" + lastRowNum);
				}

				if (cellNum >= 0) {
					Row row = sheet.getRow(rowNum);
					int lastCellNum = row.getLastCellNum() - 1;
					if (cellNum > lastCellNum) {
						throw new Exception("无效的列号" + cellNum + "，当前行的最大列号为：" + lastCellNum);
					}
				}
			}
		}
	}

	public static void main(String[] args) throws Exception {
		FileInputStream fis = new FileInputStream(new File("C:\\Documents and Settings\\Administrator\\桌面\\notice.xls"));
		ExcelReader util = new ExcelReader(fis);

		// Map<Integer, String> map = util.getDataByRow(0, 0);
		// System.out.println(map);

		// List<Map<Integer, String>> list = util.getDataBySheet(0);
		// System.out.println(list);

		String data = util.getDataByCell(0, 0, 0);
		System.out.println(data);
	}
}
